package com.totoro.web;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.totoro.domain.Project;
import com.totoro.domain.Student;
import com.totoro.domain.Tutorial;

public class TutorialExcelView extends AbstractExcelView {

	private final int COLUMNS = 6;
	@Override
	protected void buildExcelDocument(Map<String, Object> model,
			HSSFWorkbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {

		// 知道教师评审表
		Tutorial tutorial = (Tutorial) model.get("tutorial");
		// 工作量，工作态度分
		int workLoad = 0;
		// 基本能力分
		int basicAbility = 0;
		// 工作能力分
		int workAbility = 0;
		// 成果水平分
		int achievementLevel = 0;
		
		
		if (tutorial.getWorkLoad() != null) {
			workLoad = tutorial.getWorkLoad();
		}
		if(tutorial.getBasicAbility() != null){
			basicAbility = tutorial.getBasicAbility();
		}
		if(tutorial.getWorkAbility() != null){
			workAbility = tutorial.getWorkAbility();
		}
		if(tutorial.getAchievementLevel() != null){
			achievementLevel = tutorial.getAchievementLevel();
		}
		// 总成绩分
		int num = workLoad + basicAbility + workAbility + achievementLevel;

		// 毕业设计信息
		Project project = tutorial.getProject();
		// 学生信息
		Student student = project.getStudent();

		// 设置excel表的的名字
		String excelName = student.getNo() + "评审.xls";

		response.setHeader("Content-Disposition", "attachment;filename="
				+ new String(excelName.getBytes(), "iso8859-1"));

		// 创建单元格样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		HSSFCellStyle cellStyle2 = workbook.createCellStyle();

		// 指定单元格居中对齐
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		// 指定单元格垂直居中对齐
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		// 设置单元格字体
		HSSFFont font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 14);
		cellStyle.setFont(font);
		
		HSSFFont font2 = workbook.createFont();
		font2.setFontName("宋体");
		font2.setFontHeightInPoints((short)12);
		cellStyle2.setFont(font2);
		
		// 指定当单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);
		cellStyle2.setWrapText(true);
		
		//创建单元格样式
		HSSFCellStyle cellStyle3 = workbook.createCellStyle();
		//左对齐
		cellStyle3.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		cellStyle3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle3.setFont(font2);
		cellStyle3.setWrapText(true);
		
		//设置下边框
		HSSFCellStyle bottomStyle = workbook.createCellStyle();
		bottomStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		bottomStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		bottomStyle.setFont(font2);
		bottomStyle.setWrapText(true);
		bottomStyle.setBorderBottom(CellStyle.BORDER_THIN);
		
		//设置边框
		HSSFCellStyle boderStyle = workbook.createCellStyle();
		boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		boderStyle.setFont(font2);
		boderStyle.setWrapText(true);
		boderStyle.setBorderTop(CellStyle.BORDER_THIN);
		boderStyle.setBorderRight(CellStyle.BORDER_THIN);
		boderStyle.setBorderBottom(CellStyle.BORDER_THIN);
		boderStyle.setBorderLeft(CellStyle.BORDER_THIN);	
		
		//设置右边框	 		
		HSSFCellStyle rightStyle = workbook.createCellStyle();
		rightStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		rightStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		rightStyle.setFont(font2);
		rightStyle.setWrapText(true);
		rightStyle.setBorderRight(CellStyle.BORDER_THIN);
		
		//设置左边框
		HSSFCellStyle leftStyle = workbook.createCellStyle();
		leftStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		leftStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		leftStyle.setFont(font2);
		leftStyle.setWrapText(true);
		leftStyle.setBorderLeft(CellStyle.BORDER_THIN);
		
		//设置上边框
		HSSFCellStyle topStyle = workbook.createCellStyle();
		topStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		topStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		topStyle.setFont(font2);
		topStyle.setBorderTop(CellStyle.BORDER_THIN);

		HSSFSheet sheet = workbook.createSheet("tutorial");
		// Cell的宽度,正常的宽度1800左右；
		this.setColumns(sheet, 5000);
		int rowNum = 0;

		// 设置表头格式
		{
			HSSFRow title = sheet.createRow(rowNum);			
			Cell cell = title.createCell(0);			
			// 合并单元格：将0行0列至0行6列的单元格合并为一个单元格
			sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, COLUMNS-1));
			cell.setCellValue("山东建筑大学毕业设计（论文）指导教师评审表");
			cell.setCellStyle(cellStyle);
			
			// 设置行高度
			title.setHeight((short) 700);

			rowNum++;
		}

		// 题目格式
		{
			HSSFRow row = sheet.createRow(rowNum);
			for(int i=0; i<COLUMNS-1;i++){
				row.createCell(i).setCellStyle(bottomStyle);
			}
			Cell cell0 = row.createCell(0);
			cell0.setCellValue("题目：");
			cell0.setCellStyle(cellStyle2);

			// 合并1行1列1行6列单元格
			sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 1, 4));
			Cell cell1 = row.createCell(1);
			cell1.setCellValue(project.getTitle());
			cell1.setCellStyle(cellStyle2);
			cell1.setCellStyle(bottomStyle);

			// 设置行高度
			row.setHeight((short) 600);

			rowNum++;
		}

		// 班级姓名格式
		{
			HSSFRow row = sheet.createRow(rowNum);
			Cell cell0 = row.createCell(0);
			cell0.setCellValue("班级：");
			cell0.setCellStyle(cellStyle2);
			cell0.setCellStyle(bottomStyle);

			Cell cell1 = row.createCell(1);
			cell1.setCellValue(student.getStudentClass().getDescription());
			cell1.setCellStyle(cellStyle2);
			cell1.setCellStyle(bottomStyle);

			Cell cell2 = row.createCell(2);
			cell2.setCellValue("学生姓名：");
			cell2.setCellStyle(cellStyle2);

			Cell cell3 = row.createCell(3);
			cell3.setCellValue(student.getName());
			cell3.setCellStyle(cellStyle2);

			Cell cell4 = row.createCell(4);
			cell4.setCellValue("学号:");
			cell4.setCellStyle(cellStyle2);
			cell4.setCellStyle(bottomStyle);

			Cell cell5 = row.createCell(5);
			cell5.setCellValue(student.getNo());
			cell5.setCellStyle(cellStyle2);

			// 设置行高度
			row.setHeight((short) 600);

			rowNum++;
		}

		// 设置内容格式
		{
			HSSFRow row = sheet.createRow(rowNum);
			Cell cell0 = row.createCell(0);
			for(int i=0; i<COLUMNS;i++){
				row.createCell(i).setCellStyle(boderStyle);
			}
			// 合并单元格：将1行1列1行4列的单元格合并为一个单元格
			sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + 3, 0, COLUMNS-6));
			
			cell0.setCellValue("指导教师评分（每项各占10分，总计40分）");
			cell0.setCellStyle(cellStyle2);
			cell0.setCellStyle(boderStyle);

			sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 1, 3));
			Cell cell1 = row.createCell(1);
			cell1.setCellValue("工作量、工作态度");
			cell1.setCellStyle(cellStyle2);
			cell1.setCellStyle(bottomStyle);

			// 设置工作量分
			sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 4, 5));
			Cell cell4 = row.createCell(4);
			cell4.setCellValue(workLoad);
			cell4.setCellStyle(cellStyle2);
			cell4.setCellStyle(bottomStyle);
			// 设置行高度
			row.setHeight((short) 600);


			rowNum++;
		}

		// 设置内容格式
		{
			HSSFRow row = sheet.createRow(rowNum);
			for(int i=0; i<COLUMNS;i++){
				row.createCell(i).setCellStyle(boderStyle);
			}

			sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 1, COLUMNS-3));
			Cell cell1 = row.createCell(1);
			cell1.setCellValue("基本理论、基本知识、基本技能和外语水平");
			cell1.setCellStyle(cellStyle2);
			cell1.setCellStyle(bottomStyle);


			sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 4, 5));
			Cell cell4 = row.createCell(4);
			cell4.setCellValue(basicAbility);
			cell4.setCellStyle(cellStyle2);
			cell4.setCellStyle(bottomStyle);
			// 设置行高度
			row.setHeight((short) 600);

			rowNum++;
		}

		// 设置内容格式
		{
			HSSFRow row = sheet.createRow(rowNum);
			for(int i=0; i<COLUMNS;i++){
				row.createCell(i).setCellStyle(boderStyle);
			}

			sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 1, 3));
			Cell cell1 = row.createCell(1);
			cell1.setCellValue("独立工作能力、分析与解决问题的能力");
			cell1.setCellStyle(cellStyle2);
			cell1.setCellStyle(bottomStyle);

			sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 4, 5));
			Cell cell4 = row.createCell(4);
			cell4.setCellValue(workAbility);
			cell4.setCellStyle(cellStyle2);
			cell4.setCellStyle(bottomStyle);
			// 设置行高度
			row.setHeight((short) 600);

			rowNum++;
		}

		// 设置内容格式
		{
			HSSFRow row = sheet.createRow(rowNum);
			for(int i=0; i<COLUMNS;i++){
				row.createCell(i).setCellStyle(boderStyle);
			}

			sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 1, 3));
			Cell cell1 = row.createCell(1);
			cell1.setCellValue("完成任务情况及水平");
			cell1.setCellStyle(cellStyle2);
			cell1.setCellStyle(bottomStyle);

			sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 4, 5));
			Cell cell4 = row.createCell(4);
			cell4.setCellValue(achievementLevel);
			cell4.setCellStyle(cellStyle2);
			cell4.setCellStyle(bottomStyle);
			// 设置行高度
			row.setHeight((short) 600);

			rowNum++;
		}

		// 设置成绩总计格式
		{
			HSSFRow row = sheet.createRow(rowNum);
			for(int i=0; i<COLUMNS;i++){
				row.createCell(i).setCellStyle(boderStyle);
			}

			// 合并单元格5行0列5行1列
			sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 3));
			Cell cell0 = row.createCell(0);
			cell0.setCellValue("总计");
			cell0.setCellStyle(cellStyle2);
			cell0.setCellStyle(bottomStyle);

			sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 4, 5));
			Cell cell4 = row.createCell(4);
			cell4.setCellValue(num);
			cell4.setCellStyle(cellStyle2);
			cell4.setCellStyle(bottomStyle);
			// 设置行高度
			row.setHeight((short) 600);

			rowNum++;
		}
		
		//设置内容
		{
			HSSFRow row = sheet.createRow(rowNum);
			
			row.createCell(0).setCellStyle(leftStyle);
			row.createCell(5).setCellStyle(rightStyle);
		    //将6行0列至6行5列的单元格合并
			sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,0,5));
			Cell cell = row.createCell(0);
			cell.setCellValue("评审意见：");
			cell.setCellStyle(cellStyle3);
			
		
			row.setHeight((short)400);
			
			rowNum++;
			
		}

		// 设置评语格式
		{
			HSSFRow rowRemark = sheet.createRow(rowNum);
			
			rowRemark.createCell(0).setCellStyle(leftStyle);
			rowRemark.createCell(5).setCellStyle(rightStyle);
			// 合并单元格：将7行0列至7行5列的单元格合并为一个单元格
			sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 5));
			Cell cell = rowRemark.createCell(0);
			cell.setCellValue(tutorial.getRemark());
			cell.setCellStyle(cellStyle2);

			rowRemark.setHeight((short) 5000);
			
			rowNum++;
		}
		
		//设置内容
		{
			HSSFRow row = sheet.createRow(rowNum);
			
			row.createCell(0).setCellStyle(leftStyle);
			row.createCell(5).setCellStyle(rightStyle);
			
			sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,4,5));
			Cell cell = row.createCell(4);
			cell.setCellValue("评阅人签字：");
			cell.setCellStyle(cellStyle3);
						
			row.setHeight((short) 400);
			
			rowNum++;		
		}
		//设置评阅人
		{   
			HSSFRow row = sheet.createRow(rowNum);
			
			row.createCell(0).setCellStyle(leftStyle);
			row.createCell(5).setCellStyle(rightStyle);
			
			sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,4,5));
			Cell cell1 = row.createCell(4);
			cell1.setCellValue(tutorial.getTutor().getName().toString());
			cell1.setCellStyle(cellStyle2);
		
			row.setHeight((short) 400);
			
			rowNum++;		
		}
		//设置时间
		{   
			HSSFRow row = sheet.createRow(rowNum);
			for(int i=0; i<COLUMNS;i++){
				row.createCell(i).setCellStyle(bottomStyle);
			}
			row.createCell(0).setCellStyle(leftStyle);
			row.createCell(5).setCellStyle(rightStyle);
			
			sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,4,5));
			Cell dateCell = row.createCell(4);
			dateCell.setCellValue(new SimpleDateFormat("yyyy 年 MM 月 dd 日").format(new Date()));
			dateCell.setCellStyle(cellStyle2);
			dateCell.setCellStyle(bottomStyle);
						
			row.setHeight((short) 400);
			
			rowNum++;
			
			//设置下一行的上边框
			HSSFRow rowLine = sheet.createRow(rowNum);
			rowLine.createCell(0).setCellStyle(topStyle);
			rowLine.createCell(5).setCellStyle(topStyle);
			
		}

	}

	/**
	 * 设置列宽
	 * 
	 * @param sheet
	 *            工作表
	 * @param fontSize
	 *            宽度
	 */
	private void setColumns(HSSFSheet sheet, int columnWidth) {
		sheet.setColumnWidth(0, 3400);// 第一列的宽度；
		sheet.setColumnWidth(1, 3400);// 第二列的宽度；
		sheet.setColumnWidth(2, 4400);// 第三列的宽度；
		sheet.setColumnWidth(3, 4400);// 第四列的宽度；
		sheet.setColumnWidth(4, 3400);// 第五列的宽度；
		sheet.setColumnWidth(5, 4400);// 第六列的宽度；
	}

}
